/**
 * Copyright 2019 吉鼎科技.

 * <p>
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 * <p>
 * http://www.apache.org/licenses/LICENSE-2.0
 * <p>
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package cn.easyplatform.dao.impl;

import cn.easyplatform.EasyPlatformWithLabelKeyException;
import cn.easyplatform.dao.BizDao;
import cn.easyplatform.dao.DaoException;
import cn.easyplatform.dao.Page;
import cn.easyplatform.dao.dialect.Dialect;
import cn.easyplatform.dao.utils.DaoUtils;
import cn.easyplatform.dao.utils.SqlUtils;
import cn.easyplatform.dos.FieldDo;
import cn.easyplatform.dos.LogDo;
import cn.easyplatform.dos.Record;
import cn.easyplatform.dos.UserDo;
import cn.easyplatform.entities.beans.table.TableBean;
import cn.easyplatform.entities.beans.table.TableField;
import cn.easyplatform.lang.CaseInsensitiveHashMap;
import cn.easyplatform.lang.Nums;
import cn.easyplatform.lang.Strings;
import cn.easyplatform.transaction.jdbc.JdbcTransactions;
import com.alibaba.druid.sql.PagerUtils;
import org.apache.commons.io.IOUtils;
import org.apache.commons.lang3.ArrayUtils;

import javax.sql.DataSource;
import java.sql.*;
import java.util.*;

/**
 * @author <a href="mailto:davidchen@epclouds.com">littleDog</a> <br/>
 * @since 2.0.0 <br/>
 */
public abstract class AbstractBizDao extends AbstractDao implements BizDao {

    private Connection batconn;

    private Statement batstmt;

    private String batSql;

    private boolean batAutoCommit;

    private PreparedStatement stmt;

    /**
     * @param ds
     */
    public AbstractBizDao(DataSource ds) {
        super(ds);
    }

    /**
     * @return
     */
    protected abstract Dialect getDialect();

    @Override
    public boolean insert(UserDo user, TableBean table, Record record, boolean isAutoCommit) {
        StringBuilder sb1 = new StringBuilder();
        StringBuilder sb2 = new StringBuilder();
        sb1.append("insert into ").append(table.getId()).append(" (");
        Iterator<FieldDo> itr = record.getData().iterator();
        boolean autoKey = table.isAutoKey() && table.isByDb();
        while (itr.hasNext()) {
            FieldDo fd = itr.next();
            if (!autoKey || !table.getKey().get(0).equals(fd.getRawName())) {
                sb1.append(fd.getRawName());
                sb2.append("?");
                if (itr.hasNext()) {
                    sb1.append(",");
                    sb2.append(",");
                }
            }
        }
        sb1.append(") values (").append(sb2.toString()).append(")");
        sb2 = null;
        PreparedStatement pstmt = null;
        Connection conn = null;
        ResultSet rs = null;
        try {
            if (isAutoCommit)
                conn = ds.getConnection();
            else
                conn = JdbcTransactions.getConnection(ds);
            if (autoKey) {
                pstmt = conn.prepareStatement(sb1.toString(), Statement.RETURN_GENERATED_KEYS);
            } else {
                pstmt = conn.prepareStatement(sb1.toString());
            }
            itr = record.getData().iterator();
            int index = 1;
            while (itr.hasNext()) {
                FieldDo fd = itr.next();
                if (!autoKey || !table.getKey().get(0).equals(fd.getRawName())) {
                    SqlUtils.setValue(pstmt, index, fd);
                    index++;
                }
            }
            int result = pstmt.executeUpdate();
            if (result > 0) {
//                CacheObject cache = ((CacheContext) ds).getCache(table.getId());
//                if (cache != null)
//                    cache.reset();
                if (autoKey) {
                    rs = pstmt.getGeneratedKeys();
                    if (rs.next()) {
                        long id = rs.getLong(1);
                        record.get(table.getKey().get(0)).setValue(id);
                    }
                }
                if (log.isInfoEnabled())
                    log.info("insert->{},{}", sb1, record.getData());
                if (user.getLogLevel() == LogDo.LEVEL_TABLE) {
                    SqlUtils.log(conn, new LogDo(user.getEventId(), user.getId(), user.getDeviceType().getName(), LogDo.TYPE_TABLE,
                            "insert", table.getId() + ":" + record.getData().toString()));
                }
            }
            sb1 = null;
            return result > 0;
        } catch (SQLException ex) {
            if (log.isErrorEnabled())
                log.error("insert", ex);
            throw new DaoException("dao.biz.insert.error", ex, table.getId());
        } finally {
            if (rs != null)
                DaoUtils.closeQuietly(rs);
            DaoUtils.closeQuietly(pstmt);
            if (isAutoCommit)
                DaoUtils.closeQuietly(conn);
        }
    }

    @Override
    public long insert(UserDo user, String statement, boolean isAutoCommit, Object... parameter) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            if (isAutoCommit)
                conn = ds.getConnection();
            else
                conn = JdbcTransactions.getConnection(ds);
            pstmt = conn.prepareStatement(statement, Statement.RETURN_GENERATED_KEYS);
            for (int i = 1; i <= parameter.length; i++)
                pstmt.setObject(i, parameter[i - 1]);
            pstmt.executeUpdate();
            rs = pstmt.getGeneratedKeys();
            long id = 0;
            if (rs.next())
                id = rs.getLong(1);
            if (user.getLogLevel() == LogDo.LEVEL_TABLE) {
                SqlUtils.log(conn, new LogDo(user.getEventId(), user.getId(), user.getDeviceType().getName(), LogDo.TYPE_TABLE,
                        "insert", String.format("%s:%s", statement, ArrayUtils.toString(parameter))));
            }
            return id;
        } catch (SQLException ex) {
            log.error("insert", ex);
            if (statement.length() > 20)
                statement = statement.substring(0, 20) + "...";
            if (parameter.length > 5)
                parameter = ArrayUtils.subarray(parameter, 0, 5);
            throw new DaoException("dao.biz.update.sql.error", ex, statement, ArrayUtils.toString(parameter));
        } finally {
            DaoUtils.closeQuietly(pstmt, rs);
            if (isAutoCommit)
                DaoUtils.closeQuietly(conn);
        }
    }

    @Override
    public int update(UserDo user, String statement, boolean isAutoCommit, Object... parameter) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            if (isAutoCommit)
                conn = ds.getConnection();
            else
                conn = JdbcTransactions.getConnection(ds);
            pstmt = conn.prepareStatement(statement);
            for (int i = 1; i <= parameter.length; i++)
                pstmt.setObject(i, parameter[i - 1]);
            int rs = pstmt.executeUpdate();
            if (user != null && user.getLogLevel() == LogDo.LEVEL_TABLE) {
                SqlUtils.log(conn, new LogDo(user.getEventId(), user.getId(), user.getDeviceType().getName(), LogDo.TYPE_TABLE,
                        "update", String.format("%s:%s", statement, ArrayUtils.toString(parameter))));
            }
            return rs;
        } catch (SQLException ex) {
            log.error("update", ex);
            if (statement.length() > 20)
                statement = statement.substring(0, 20) + "...";
            if (parameter.length > 5)
                parameter = ArrayUtils.subarray(parameter, 0, 5);
            throw new DaoException("dao.biz.update.sql.error", ex, statement, ArrayUtils.toString(parameter));
        } finally {
            DaoUtils.closeQuietly(pstmt);
            if (isAutoCommit)
                DaoUtils.closeQuietly(conn);
        }
    }

    @Override
    public boolean update(UserDo user, String table, Record record, boolean isAutoCommit) {
        if (record.getKey() == null || record.getKey().isEmpty())
            throw new DaoException("dao.biz.key.not.found", table);
        StringBuilder sb = new StringBuilder();
        sb.append("update ").append(table).append(" set ");
        Iterator<FieldDo> itr = record.getData().iterator();
        while (itr.hasNext()) {
            FieldDo fd = itr.next();
            sb.append(fd.getRawName()).append("=?");
            if (itr.hasNext())
                sb.append(",");
        }
        sb.append(" where ");
        Iterator<String> keyItr = record.getKey().iterator();
        while (keyItr.hasNext()) {
            sb.append(keyItr.next()).append("=?");
            if (keyItr.hasNext())
                sb.append(" and ");
        }
        if (log.isInfoEnabled())
            log.info("update->{},{}", sb, record.getData());
        PreparedStatement pstmt = null;
        Connection conn = null;
        try {
            if (isAutoCommit)
                conn = ds.getConnection();
            else
                conn = JdbcTransactions.getConnection(ds);
            pstmt = conn.prepareStatement(sb.toString());
            itr = record.getData().iterator();
            int index = 1;
            while (itr.hasNext()) {
                SqlUtils.setValue(pstmt, index, itr.next());
                index++;
            }
            keyItr = record.getKey().iterator();
            while (keyItr.hasNext()) {
                String name = keyItr.next();
                FieldDo fd = record.get(name);
                if (fd == null)
                    throw new EasyPlatformWithLabelKeyException("dao.biz.key.name.not.found", table, name);
                SqlUtils.setValue(pstmt, index, fd);
                index++;
            }
            int result = pstmt.executeUpdate();
//            if (result > 0) {
//                CacheObject cache = ((CacheContext) ds).getCache(table);
//                if (cache != null)
//                    cache.clear();
//            }
            if (user.getLogLevel() == LogDo.LEVEL_TABLE) {
                sb.append(record.getData());
                SqlUtils.log(conn, new LogDo(user.getEventId(), user.getId(), user.getDeviceType().getName(), LogDo.TYPE_TABLE,
                        "update", sb.toString()));
            }
            sb = null;
            return result > 0;
        } catch (SQLException ex) {
            if (log.isErrorEnabled())
                log.error("update", ex);
            throw new DaoException("dao.biz.update.error", ex, table);
        } finally {
            DaoUtils.closeQuietly(pstmt);
            if (isAutoCommit)
                DaoUtils.closeQuietly(conn);
        }
    }

    @Override
    public int update(UserDo user, String statement, List<FieldDo> parameter, boolean isAutoCommit) {
        PreparedStatement pstmt = null;
        Connection conn = null;
        try {
            if (isAutoCommit)
                conn = ds.getConnection();
            else
                conn = JdbcTransactions.getConnection(ds);
            if (log.isInfoEnabled())
                log.info("update->{},{}", statement, parameter);
            pstmt = conn.prepareStatement(statement);
            if (parameter != null) {
                Iterator<FieldDo> itr = parameter.iterator();
                int index = 1;
                while (itr.hasNext()) {
                    SqlUtils.setValue(pstmt, index, itr.next());
                    index++;
                }
            }
            int result = pstmt.executeUpdate();
            if (user != null && user.getLogLevel() == LogDo.LEVEL_TABLE) {
                StringBuilder sb = new StringBuilder();
                sb.append(statement).append(parameter);
                SqlUtils.log(conn, new LogDo(user.getEventId(), user.getId(), user.getDeviceType().getName(), LogDo.TYPE_TABLE,
                        "update", sb.toString()));
                sb = null;
            }
            return result;
        } catch (SQLException ex) {
            if (log.isErrorEnabled())
                log.error("update", ex);
            if (statement.length() > 20)
                statement = statement.substring(0, 20) + "...";
            if (parameter.size() > 5)
                parameter = parameter.subList(0, 5);
            throw new DaoException("dao.biz.update.sql.error", ex, statement, parameter);
        } finally {
            DaoUtils.closeQuietly(pstmt);
            if (isAutoCommit)
                DaoUtils.closeQuietly(conn);
        }
    }

    @Override
    public void prepareBatch(String statement, boolean isAutoCommit) {
        try {
            if (isAutoCommit) {
                batconn = ds.getConnection();
                batconn.setAutoCommit(false);
            } else
                batconn = JdbcTransactions.getConnection(ds);
            this.batAutoCommit = isAutoCommit;
            if (!Strings.isBlank(statement)) {
                batstmt = batconn.prepareStatement(statement);
                batSql = statement;
            } else
                batstmt = batconn.createStatement();
        } catch (SQLException ex) {
            if (log.isErrorEnabled())
                log.error("prepareBatch", ex);
            DaoUtils.closeQuietly(batstmt);
            if (isAutoCommit) {
                try {
                    batconn.setAutoCommit(true);
                } catch (Exception e) {
                }
                DaoUtils.closeQuietly(batconn);
            }
            if (statement.length() > 20)
                statement = statement.substring(0, 20) + "...";
            throw new DaoException("dao.biz.pre.batch", statement, ex);
        }
    }

    @Override
    public void addBatch(UserDo user, Collection<FieldDo> parameter) {
        try {
            Iterator<FieldDo> itr = parameter.iterator();
            int index = 1;
            while (itr.hasNext())
                SqlUtils.setValue((PreparedStatement) batstmt, index++, itr.next());
            ((PreparedStatement) batstmt).addBatch();
            if (user.getLogLevel() == LogDo.LEVEL_TABLE) {
                StringBuilder sb = new StringBuilder();
                sb.append(batSql).append(parameter);
                SqlUtils.log(batconn, new LogDo(user.getEventId(), user.getId(), user.getDeviceType().getName(),
                        LogDo.TYPE_TABLE, "addBatch", sb.toString()));
                sb = null;
            }
        } catch (SQLException ex) {
            if (log.isErrorEnabled())
                log.error("addBatch", ex);
            DaoUtils.closeQuietly(batstmt);
            if (batAutoCommit) {
                try {
                    batconn.setAutoCommit(true);
                } catch (Exception e) {
                }
                DaoUtils.closeQuietly(batconn);
            }
            throw new DaoException("dao.biz.add.batch1", parameter, ex);
        }
    }

    @Override
    public void addBatch(UserDo user, String sql) {
        try {
            batstmt.addBatch(sql);
            if (user.getLogLevel() == LogDo.LEVEL_TABLE) {
                StringBuilder sb = new StringBuilder();
                sb.append(batSql).append(sql);
                SqlUtils.log(batconn, new LogDo(user.getEventId(), user.getId(), user.getDeviceType().getName(),
                        LogDo.TYPE_TABLE, "addBatch", sb.toString()));
                sb = null;
            }
        } catch (SQLException ex) {
            if (log.isErrorEnabled())
                log.error("addBatch", ex);
            DaoUtils.closeQuietly(batstmt);
            if (batAutoCommit) {
                try {
                    batconn.setAutoCommit(true);
                } catch (Exception e) {
                }
                DaoUtils.closeQuietly(batconn);
            }
            throw new DaoException("dao.biz.add.batch2", sql, ex);
        }
    }

    @Override
    public void executeBatch() {
        if (batstmt != null && batconn != null) {
            try {
                batstmt.executeBatch();
                if (batAutoCommit)
                    batconn.commit();
            } catch (SQLException ex) {
                if (log.isErrorEnabled())
                    log.error("executeBatch", ex);
                throw new DaoException("dao.biz.execute.batch", ex);
            } finally {
                DaoUtils.closeQuietly(batstmt);
                if (batAutoCommit) {
                    try {
                        batconn.setAutoCommit(true);
                    } catch (Exception e) {
                    }
                    DaoUtils.closeQuietly(batconn);
                }
            }
        }
    }

    @Override
    public boolean delete(UserDo user, String table, List<FieldDo> key, boolean isAutoCommit) {
        if (key == null || key.isEmpty())
            throw new DaoException("dao.biz.key.not.found", table);
        StringBuilder sb = new StringBuilder();
        sb.append("delete from ").append(table).append(" where ");
        Iterator<FieldDo> itr = key.iterator();
        while (itr.hasNext()) {
            FieldDo fd = itr.next();
            sb.append(fd.getRawName()).append("=?");
            if (itr.hasNext())
                sb.append(" and ");
        }
        PreparedStatement pstmt = null;
        if (log.isInfoEnabled())
            log.info("delete->{},{}", sb, key);
        Connection conn = null;
        try {
            if (isAutoCommit)
                conn = ds.getConnection();
            else
                conn = JdbcTransactions.getConnection(ds);
            pstmt = conn.prepareStatement(sb.toString());
            itr = key.iterator();
            int index = 1;
            while (itr.hasNext()) {
                SqlUtils.setValue(pstmt, index, itr.next());
                index++;
            }
            int result = pstmt.executeUpdate();
//            if (result > 0) {
//                CacheObject cache = ((CacheContext) ds).getCache(table);
//                if (cache != null)
//                    cache.remove(key);
//            }
            if (user.getLogLevel() == LogDo.LEVEL_TABLE) {
                sb.append(key);
                SqlUtils.log(conn, new LogDo(user.getEventId(), user.getId(), user.getDeviceType().getName(), LogDo.TYPE_TABLE,
                        "delete", sb.toString()));
            }
            sb = null;
            return result > 0;
        } catch (SQLException ex) {
            if (log.isErrorEnabled())
                log.error("delete", ex);
            throw new DaoException("dao.biz.delete.error", ex, table);
        } finally {
            DaoUtils.closeQuietly(pstmt);
            if (isAutoCommit)
                DaoUtils.closeQuietly(conn);
        }
    }

    @Override
    public List<FieldDo[]> selectList(String statement, List<FieldDo> parameter) {
//        Object[] cache = CacheUtils.getCache(ds, statement, parameter);
//        if (cache != null) {
//            CacheObject co = (CacheObject) cache[0];
//            Object obj = co.get((CacheKey) cache[1]);
//            if (obj != null) {
//                if (obj instanceof List<?>) {
//                    List<FieldDo[]> result = (List<FieldDo[]>) obj;
//                    if (log.isInfoEnabled())
//                        log.infof("getList from cache ->{},size:{}", cache[1], result.size());
//                    return result;
//                } else {
//                    if (log.isInfoEnabled())
//                        log.infof("getList from cache ->{},value:{}", cache[1], obj);
//                    if (obj instanceof FieldDo[]) {
//                        List<FieldDo[]> result = new ArrayList<>();
//                        result.add((FieldDo[]) obj);
//                        return result;
//                    } else if (obj instanceof FieldDo) {
//                        List<FieldDo[]> result = new ArrayList<>();
//                        result.add(new FieldDo[]{(FieldDo) obj});
//                        return result;
//                    } else
//                        return Collections.emptyList();
//                }
//            }
//        }
        if (log.isInfoEnabled())
            log.info("getList->{},{}", statement, parameter);
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            Connection conn = JdbcTransactions.getConnection(ds);
            pstmt = conn.prepareStatement(statement);
            if (parameter != null && !parameter.isEmpty()) {
                Iterator<FieldDo> itr = parameter.iterator();
                int index = 1;
                while (itr.hasNext())
                    SqlUtils.setValue(pstmt, index++, itr.next());
            }
            rs = pstmt.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            List<FieldDo[]> loadedObjects = new ArrayList<FieldDo[]>();
            int size = rsmd.getColumnCount();
            while (rs.next()) {
                FieldDo[] record = new FieldDo[size];
                for (int index = 1; index <= size; index++)
                    record[index - 1] = SqlUtils.getSqlValue(rs, rsmd, index);
                loadedObjects.add(record);
            }
//            if (cache != null) {
//                if (log.isInfoEnabled())
//                    log.infof("getList put cache ->{},size:{}", cache[1], loadedObjects.size());
//                CacheObject co = (CacheObject) cache[0];
//                co.put((CacheKey) cache[1], loadedObjects);
//            }
            return loadedObjects;
        } catch (SQLException ex) {
            //log.error("selectList", ex);
            throw new DaoException("dao.biz.query.error", ex, statement, parameter);
        } finally {
            DaoUtils.closeQuietly(pstmt, rs);
        }
    }

    @Override
    public Map<String, Object> selectDictionary(String statement, Object... parameter) {
        if (log.isInfoEnabled())
            log.info("selectDictionary->{},{}", statement, Arrays.toString(parameter));
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            Connection conn = JdbcTransactions.getConnection(ds);
            pstmt = conn.prepareStatement(statement);
            SqlUtils.setParameter(pstmt, parameter);
            rs = pstmt.executeQuery();
            Map<String, Object> loadedObjects = new HashMap<>();
            while (rs.next()) {
                Object key = rs.getObject(1);
                loadedObjects.put(key == null ? "" : key.toString(), SqlUtils.convert(rs.getObject(2)));
            }
            return loadedObjects;
        } catch (Exception ex) {
            log.error("selectDictionary", ex);
            throw new DaoException("dao.biz.query.error", ex, statement, parameter);
        } finally {
            DaoUtils.closeQuietly(pstmt, rs);
        }
    }

    @Override
    public List<FieldDo[]> selectList(String statement, List<FieldDo> parameter, Page page) {
        if (page == null)
            return selectList(statement, parameter);
        if (log.isInfoEnabled())
            log.info("getList->{},{},{},{},{}", statement, page.getOrderBy(), page.getPageNo(), page.getPageSize(), parameter);
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            Connection conn = JdbcTransactions.getConnection(ds);
            if (page.isGetTotal()) {
                String dbType = DaoUtils.getDatabaseType(ds.toString());
                String sql = PagerUtils.count(statement, dbType);
                if (log.isInfoEnabled())
                    log.info("getTotal:{}", sql);
                pstmt = conn.prepareStatement(sql);
                if (parameter != null && !parameter.isEmpty()) {
                    Iterator<FieldDo> itr = parameter.iterator();
                    int index = 1;
                    while (itr.hasNext())
                        SqlUtils.setValue(pstmt, index++, itr.next());
                }
                rs = pstmt.executeQuery();
                rs.next();
                page.setTotalCount(Nums.toInt(rs.getObject(1), 0));
                DaoUtils.closeQuietly(pstmt, rs);
                if (page.getTotalCount() == 0)
                    return Collections.emptyList();
            }
            StringBuilder sb = new StringBuilder();
            sb.append(statement);
            if (!Strings.isBlank(page.getOrderBy()))
                sb.append(" order by ").append(page.getOrderBy());
            statement = getDialect().getPageSql(sb.toString(), page);

            pstmt = conn.prepareStatement(statement);
            if (parameter != null && !parameter.isEmpty()) {
                Iterator<FieldDo> itr = parameter.iterator();
                int index = 1;
                while (itr.hasNext())
                    SqlUtils.setValue(pstmt, index++, itr.next());
            }
            rs = pstmt.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            List<FieldDo[]> loadedObjects = new ArrayList<FieldDo[]>();
            int size = rsmd.getColumnCount();
            while (rs.next()) {
                FieldDo[] record = new FieldDo[size];
                for (int index = 1; index <= size; index++)
                    record[index - 1] = SqlUtils.getValue(rs, rsmd, index);
                loadedObjects.add(record);
            }
            return loadedObjects;
        } catch (SQLException ex) {
            log.error("selectList", ex);
            throw new DaoException("dao.biz.query.error", ex, statement, parameter);
        } finally {
            DaoUtils.closeQuietly(pstmt, rs);
        }
    }

    public int getCount(String statement, List<FieldDo> parameter) {
//        Object[] cache = CacheUtils.getCache(ds, statement, parameter);
//        if (cache != null) {
//            CacheObject co = (CacheObject) cache[0];
//            Object obj = co.get((CacheKey) cache[1]);
//            if (obj != null) {
//                if (log.isInfoEnabled())
//                    log.infof("getCount from cache ->{},,size:{}", cache[1], obj);
//                if (obj instanceof List<?>) {
//                    List<FieldDo[]> result = (List<FieldDo[]>) obj;
//                    return result.size();
//                }
//            }
//        }
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            Connection conn = JdbcTransactions.getConnection(ds);
            String dbType = DaoUtils.getDatabaseType(ds.toString());
            String sql = PagerUtils.count(statement, dbType);
            if (log.isInfoEnabled())
                log.info("getCount:{}", sql);
            pstmt = conn.prepareStatement(sql);
            if (parameter != null && !parameter.isEmpty()) {
                Iterator<FieldDo> itr = parameter.iterator();
                int index = 1;
                while (itr.hasNext())
                    SqlUtils.setValue(pstmt, index++, itr.next());
            }
            rs = pstmt.executeQuery();
            rs.next();
            Number num = (Number) rs.getObject(1);
            return num.intValue();
        } catch (SQLException ex) {
            log.error("getCount", ex);
            throw new DaoException("dao.biz.query.error", ex, statement, parameter);
        } finally {
            DaoUtils.closeQuietly(pstmt, rs);
        }
    }

    @Override
    public Record selectByKey(TableBean table, List<FieldDo> key) {
//        CacheObject cache = ((CacheContext) ds).getCache(table.getId());
//        if (cache != null) {
//            FieldDo[] data = cache.get(key);
//            if (data != null) {
//                if (log.isInfoEnabled())
//                    log.infof("selectByKey from cache->{},{}", table.getId(), key);
//                Record record = new Record();
//                record.setKey(table.getKey());
//                for (FieldDo field : data)
//                    record.set(field.clone());
//                return record;
//            }
//        }
        if (log.isInfoEnabled())
            log.info("selectByKey->{},{}", table.getId(), key);
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            Connection conn = JdbcTransactions.getConnection(ds);
            StringBuilder sb = new StringBuilder();
            sb.append("select ");
            Iterator<TableField> itr = table.getFields().iterator();
            while (itr.hasNext()) {
                sb.append(itr.next().getName());
                if (itr.hasNext())
                    sb.append(",");
            }
            sb.append(" from ").append(table.getId()).append(" where ");
            Iterator<FieldDo> keyItr = key.iterator();
            while (keyItr.hasNext()) {
                FieldDo fd = keyItr.next();
                sb.append(fd.getRawName()).append("=?");
                if (keyItr.hasNext())
                    sb.append(" and ");
            }
            pstmt = conn.prepareStatement(sb.toString());
            sb = null;
            keyItr = key.iterator();
            int index = 1;
            while (keyItr.hasNext())
                SqlUtils.setValue(pstmt, index++, keyItr.next());
            rs = pstmt.executeQuery();
            if (rs.next()) {
                Record record = new Record();
                record.setKey(table.getKey());
                int size = table.getFields().size();
                for (index = 1; index <= size; index++)
                    record.set(SqlUtils.getValue(rs, index, table.getFields().get(index - 1)));
//                if (cache != null) //插入到缓存中
//                    cache.put(record.getData());
                return record;
            }
            return null;
        } catch (SQLException ex) {
            log.error("selectByKey", ex);
            throw new DaoException("dao.biz.select.one.error", ex, table, key);
        } finally {
            DaoUtils.closeQuietly(pstmt, rs);
        }
    }

    @Override
    public FieldDo[] selectOne(String statement, List<FieldDo> parameter) {
//        Object[] cache = CacheUtils.getCache(ds, statement, parameter);
//        if (cache != null) {
//            CacheObject co = (CacheObject) cache[0];
//            Object obj = co.get((CacheKey) cache[1]);
//            if (obj != null) {
//                if (log.isInfoEnabled())
//                    log.infof("selectOne from cache ->{},,size:{}", cache[1], obj);
//                if (obj instanceof List<?>) {
//                    List<FieldDo[]> result = (List<FieldDo[]>) obj;
//                    if (!result.isEmpty())
//                        return result.get(0);
//                } else if (obj instanceof FieldDo) {
//                    return new FieldDo[]{(FieldDo) obj};
//                } else if (obj instanceof FieldDo[])
//                    return (FieldDo[]) obj;
//            }
//        }
        if (log.isInfoEnabled())
            log.info("selectOne->{},{}", statement, parameter);
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            Connection conn = JdbcTransactions.getConnection(ds);
            pstmt = conn.prepareStatement(statement);
            int index = 1;
            if (parameter != null) {
                Iterator<FieldDo> itr = parameter.iterator();
                while (itr.hasNext())
                    SqlUtils.setValue(pstmt, index++, itr.next());
            }
            rs = pstmt.executeQuery();
            if (rs.next()) {
                ResultSetMetaData rsmd = rs.getMetaData();
                int size = rsmd.getColumnCount();
                FieldDo[] record = new FieldDo[size];
                for (index = 1; index <= size; index++)
                    record[index - 1] = SqlUtils.getValue(rs, rsmd, index);
//                if (cache != null) {
//                    if (log.isInfoEnabled())
//                        log.infof("selectOne put cache ->{},size:{}", cache[1], record.length);
//                    CacheObject co = (CacheObject) cache[0];
//                    co.put((CacheKey) cache[1], record);
//                }
                return record;
            }
            return null;
        } catch (SQLException ex) {
            log.error("selectOne", ex);
            throw new DaoException("dao.biz.select.one.error", ex, statement, parameter);
        } finally {
            DaoUtils.closeQuietly(pstmt, rs);
        }
    }

    @Override
    public FieldDo selectObject(String statement, List<FieldDo> parameter) {
//        Object[] cache = CacheUtils.getCache(ds, statement, parameter);
//        if (cache != null) {
//            CacheObject co = (CacheObject) cache[0];
//            Object obj = co.get((CacheKey) cache[1]);
//            if (obj != null) {
//                if (log.isInfoEnabled())
//                    log.infof("selectObject from cache ->{},,value:{}", cache[1], obj);
//                if (obj instanceof List<?>) {
//                    List<FieldDo[]> result = (List<FieldDo[]>) obj;
//                    if (!result.isEmpty())
//                        return result.get(0)[0];
//                } else if (obj instanceof FieldDo[]) {
//                    FieldDo[] result = (FieldDo[]) obj;
//                    return result[0];
//                } else if (obj instanceof FieldDo)
//                    return (FieldDo) obj;
//            }
//        }
        if (log.isInfoEnabled())
            log.info("selectObject->{},{}", statement, parameter);
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            Connection conn = JdbcTransactions.getConnection(ds);
            pstmt = conn.prepareStatement(statement);
            if (parameter != null) {
                Iterator<FieldDo> itr = parameter.iterator();
                int index = 1;
                while (itr.hasNext())
                    SqlUtils.setValue(pstmt, index++, itr.next());
            }
            rs = pstmt.executeQuery();
            if (rs.next()) {
                ResultSetMetaData rsmd = rs.getMetaData();
                FieldDo fd = SqlUtils.getValue(rs, rsmd, 1);
//                if (cache != null) {
//                    if (log.isInfoEnabled())
//                        log.infof("selectObject put cache ->{},value:{}", cache[1], fd.getValue());
//                    CacheObject co = (CacheObject) cache[0];
//                    co.put((CacheKey) cache[1], fd);
//                }
                return fd;
            }
            return null;
        } catch (SQLException ex) {
            log.error("selectObject", ex);
            throw new DaoException("dao.biz.select.one.error", ex, statement, parameter);
        } finally {
            DaoUtils.closeQuietly(pstmt, rs);
        }
    }

    @Override
    public FieldDo[] getMetaData(String statement) {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            Connection conn = JdbcTransactions.getConnection(ds);
            pstmt = conn.prepareStatement(statement);
            rs = pstmt.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            FieldDo[] fvs = new FieldDo[rsmd.getColumnCount()];
            for (int i = 1; i <= fvs.length; i++) {
                String name = null;
                if (rsmd.getColumnLabel(i) != null)
                    name = rsmd.getColumnLabel(i);
                else
                    name = rsmd.getColumnName(i);
                FieldDo fv = new FieldDo(SqlUtils.getType(name, rsmd.getColumnType(i)));
                fv.setName(name);
                fv.setDecimal(rsmd.getScale(i));
                fv.setLength(rsmd.getColumnDisplaySize(i));
                fvs[i - 1] = fv;
            }
            return fvs;
        } catch (SQLException ex) {
            log.error("getFieldTypes", ex);
            throw new DaoException("dao.biz.select.one.error", ex, statement);
        } finally {
            DaoUtils.closeQuietly(pstmt, rs);
        }
    }

    @Override
    public ResultSet executeQuery(String statement, List<FieldDo> parameter) throws SQLException {
        Connection conn = JdbcTransactions.getConnection(ds);
        stmt = conn.prepareStatement(statement);
        if (parameter != null) {
            Iterator<FieldDo> itr = parameter.iterator();
            int index = 1;
            while (itr.hasNext())
                SqlUtils.setValue(stmt, index++, itr.next());
        }
        return stmt.executeQuery();
    }

    @Override
    public void close(ResultSet rs) {
        DaoUtils.closeQuietly(stmt, rs);
        if (batstmt != null) {
            DaoUtils.closeQuietly(batstmt);
            batstmt = null;
        }
        if (batAutoCommit && batconn != null) {
            try {
                batconn.setAutoCommit(true);
            } catch (Exception e) {
            }
            DaoUtils.closeQuietly(batconn);
        }
        stmt = null;
        rs = null;
    }


    @Override
    public void log(LogDo log) {
        try {
            Connection conn = JdbcTransactions.getConnection(ds);
            SqlUtils.log(conn, log);
        } catch (SQLException ex) {
            throw new DaoException("dao.biz.insert.error", ex, log.getUserId());
        }
    }


    @Override
    public Object selectObject(String statement, Object... parameter) {
        if (log.isInfoEnabled())
            log.info("selectObject->{},{}", statement, parameter);
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            Connection conn = JdbcTransactions.getConnection(ds);
            pstmt = conn.prepareStatement(statement);
            SqlUtils.setParameter(pstmt, parameter);
            rs = pstmt.executeQuery();
            if (rs.next())
                return SqlUtils.convert(rs.getObject(1));
            return null;
        } catch (SQLException ex) {
            log.error("selectObject", ex);
            throw new DaoException("dao.biz.select.one.error", ex, statement, parameter);
        } finally {
            DaoUtils.closeQuietly(pstmt, rs);
        }
    }

    @Override
    public Object[] selectOne(String statement, Object... parameter) {
        if (log.isInfoEnabled())
            log.info("selectOne->{},{}", statement, parameter);
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            Connection conn = JdbcTransactions.getConnection(ds);
            pstmt = conn.prepareStatement(statement);
            SqlUtils.setParameter(pstmt, parameter);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                ResultSetMetaData rsmd = rs.getMetaData();
                int size = rsmd.getColumnCount();
                Object[] record = new Object[size];
                for (int index = 1; index <= size; index++)
                    record[index - 1] = SqlUtils.convert(rs.getObject(index));
                return record;
            }
            return null;
        } catch (SQLException ex) {
            log.error("selectOne", ex);
            throw new DaoException("dao.biz.select.one.error", ex, statement, parameter);
        } finally {
            DaoUtils.closeQuietly(pstmt, rs);
        }
    }

    @Override
    public List<Object[]> selectList(String statement, Page page, Object... parameter) {
        if (log.isInfoEnabled())
            log.info("selectList->{},{}", statement, Arrays.toString(parameter));
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            Connection conn = JdbcTransactions.getConnection(ds);
            if (page != null) {
                String dbType = DaoUtils.getDatabaseType(ds.toString());
                if (page.isGetTotal()) {
                    String sql = PagerUtils.count(statement, dbType);
                    if (log.isInfoEnabled())
                        log.info("getTotal:{}", sql);
                    pstmt = conn.prepareStatement(sql);
                    SqlUtils.setParameter(pstmt, parameter);
                    rs = pstmt.executeQuery();
                    rs.next();
                    page.setTotalCount(Nums.toInt(rs.getObject(1), 0));
                    DaoUtils.closeQuietly(pstmt, rs);
                    if (page.getTotalCount() == 0)
                        return Collections.emptyList();
                }
                if (!Strings.isBlank(page.getOrderBy())) {
                    StringBuilder sb = new StringBuilder();
                    sb.append(statement);
                    sb.append(" order by ").append(page.getOrderBy());
                    statement = getDialect().getPageSql(sb.toString(), page);
                } else
                    statement = getDialect().getPageSql(statement, page);
            }
            pstmt = conn.prepareStatement(statement);
            SqlUtils.setParameter(pstmt, parameter);
            rs = pstmt.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            List<Object[]> loadedObjects = new ArrayList<>();
            int size = rsmd.getColumnCount();
            while (rs.next()) {
                Object[] record = new Object[size];
                for (int index = 1; index <= size; index++)
                    record[index - 1] = SqlUtils.convert(rs.getObject(index));
                loadedObjects.add(record);
            }
            return loadedObjects;
        } catch (SQLException ex) {
            log.error("selectMapList", ex);
            throw new DaoException("dao.biz.query.error", ex, statement, parameter);
        } finally {
            DaoUtils.closeQuietly(pstmt, rs);
        }
    }


    @Override
    public Map<String, Object> selectMap(String statement, Object... parameter) {
        if (log.isInfoEnabled())
            log.info("selectOne->{},{}", statement, parameter);
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            Connection conn = JdbcTransactions.getConnection(ds);
            pstmt = conn.prepareStatement(statement);
            SqlUtils.setParameter(pstmt, parameter);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                ResultSetMetaData rsmd = rs.getMetaData();
                int size = rsmd.getColumnCount();
                Map<String, Object> record = new CaseInsensitiveHashMap();
                for (int index = 1; index <= size; index++)
                    record.put(rsmd.getColumnLabel(index), SqlUtils.convert(rs.getObject(index)));
                return record;
            }
            return null;
        } catch (SQLException ex) {
            log.error("selectOne", ex);
            throw new DaoException("dao.biz.select.one.error", ex, statement, parameter);
        } finally {
            DaoUtils.closeQuietly(pstmt, rs);
        }
    }

    @Override
    public List<Map<String, Object>> selectMapList(String statement, Page page, Object... parameter) {
        if (log.isInfoEnabled())
            log.info("selectList->{},{}", statement, Arrays.toString(parameter));
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            Connection conn = JdbcTransactions.getConnection(ds);
            if (page != null) {
                String dbType = DaoUtils.getDatabaseType(ds.toString());
                if (page.isGetTotal()) {
                    String sql = PagerUtils.count(statement, dbType);
                    if (log.isInfoEnabled())
                        log.info("getTotal:{}", sql);
                    pstmt = conn.prepareStatement(sql);
                    SqlUtils.setParameter(pstmt, parameter);
                    rs = pstmt.executeQuery();
                    rs.next();
                    page.setTotalCount(Nums.toInt(rs.getObject(1), 0));
                    DaoUtils.closeQuietly(pstmt, rs);
                    if (page.getTotalCount() == 0)
                        return Collections.emptyList();
                }
                if (!Strings.isBlank(page.getOrderBy())) {
                    StringBuilder sb = new StringBuilder();
                    sb.append(statement);
                    sb.append(" order by ").append(page.getOrderBy());
                    statement = getDialect().getPageSql(sb.toString(), page);
                } else
                    statement = getDialect().getPageSql(statement, page);
            }
            pstmt = conn.prepareStatement(statement);
            SqlUtils.setParameter(pstmt, parameter);
            rs = pstmt.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            List<Map<String, Object>> loadedObjects = new ArrayList<>();
            int size = rsmd.getColumnCount();
            while (rs.next()) {
                Map<String, Object> record = new CaseInsensitiveHashMap();
                for (int index = 1; index <= size; index++)
                    record.put(rsmd.getColumnLabel(index), SqlUtils.convert(rs.getObject(index)));
                loadedObjects.add(record);
            }
            return loadedObjects;
        } catch (SQLException ex) {
            log.error("selectMapList", ex);
            throw new DaoException("dao.biz.query.error", ex, statement, parameter);
        } finally {
            DaoUtils.closeQuietly(pstmt, rs);
        }
    }

    @Override
    public int update(String statement, UserDo user, Object... parameter) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = JdbcTransactions.getConnection(ds);
            pstmt = conn.prepareStatement(statement);
            SqlUtils.setParameter(pstmt, parameter);
            int result = pstmt.executeUpdate();
            if (user.getLogLevel() == LogDo.LEVEL_TABLE) {
                SqlUtils.log(conn, new LogDo(user.getEventId(), user.getId(), user.getDeviceType().getName(), LogDo.TYPE_TABLE,
                        "update", String.format("%s , %s", statement, parameter == null ? "[]" : Arrays.toString(parameter))));
            }
            return result;
        } catch (SQLException ex) {
            log.error("update", ex);
            if (statement.length() > 20)
                statement = statement.substring(0, 20) + "...";
            if (parameter.length > 5)
                parameter = ArrayUtils.subarray(parameter, 0, 5);
            throw new DaoException("dao.biz.update.sql.error", ex, statement, parameter);
        } finally {
            DaoUtils.closeQuietly(pstmt);
        }
    }
}
